<?php 
/**
 *客户管理
*/

namespace app\admin\controller\Record;

use app\admin\controller\Admin;
use think\facade\Db;
use think\facade\Cache;

class Finance extends Admin {


	/*入款结算*/
	function depositSettlement(){
      
        if (!$this->request->isAjax()){
			return $this->display('depositSettlement');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$customer_idcard= $this->request->param('customer_idcard', '', 'strip_tags,trim');
			$customer_passort = $this->request->param('customer_passort', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
            if(!empty($customer_idcard)){
                $where[]= ['customer_idcard','like','%'.$customer_idcard.'%'];
            }
            if(!empty($customer_passort)){
                $where[] = ['customer_passort','like','%'.$customer_passort.'%'];
            }
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('deposit_settlement')->where($where)->count();
				$list =  Db::name('deposit_settlement')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
					$customer_ids=array_column($list,'customer_id');
					$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile','id');
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
						$list[$key]['mobile']=$customer_list[$value['customer_id']]['mobile'];
						$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	
	/*绩效管理*/
	function performanceManage(){
      
        if (!$this->request->isAjax()){
			return $this->display('performanceManage');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$customer_idcard= $this->request->param('customer_idcard', '', 'strip_tags,trim');
			$director_id= $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
            if(!empty($customer_idcard)){
                $where[]= ['customer_idcard','like','%'.$customer_idcard.'%'];
            }
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('performance_log')->where($where)->count();
				$list =  Db::name('performance_log')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
					$customer_ids=array_column($list,'customer_id');
					$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile,work_year,work_month,director_id,director,is_rename,is_hz_dg,is_yhb_dg','id');
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
						$list[$key]['mobile']=$customer_list[$value['customer_id']]['mobile'];
						$list[$key]['director']=$customer_list[$value['customer_id']]['director'];
						$list[$key]['is_rename']=$customer_list[$value['customer_id']]['is_rename'];
						$list[$key]['is_hz_dg']=$customer_list[$value['customer_id']]['is_hz_dg'];
						$list[$key]['is_yhb_dg']=$customer_list[$value['customer_id']]['is_yhb_dg'];
						$list[$key]['work_year_month'] = $customer_list[$value['customer_id']]['work_year'].'年'.$customer_list[$value['customer_id']]['work_month'].'个月';
						$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}



	/*业务所得*/
	function businessIncome(){
      
        if (!$this->request->isAjax()){
			return $this->display('businessIncome');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$customer_idcard= $this->request->param('customer_idcard', '', 'strip_tags,trim');
			$director_id= $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
            if(!empty($customer_idcard)){
                $where[]= ['customer_idcard','like','%'.$customer_idcard.'%'];
            }
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }            

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('business_income')->where($where)->count();
				$list =  Db::name('business_income')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
					$customer_ids=array_column($list,'customer_id');
					$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile,work_year,work_month,director_id,director,is_rename,is_hz_dg,is_yhb_dg','id');
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
						$list[$key]['mobile']=$customer_list[$value['customer_id']]['mobile'];
						$list[$key]['director']=$customer_list[$value['customer_id']]['director'];
						$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*其他支出*/
	function otherExpense(){
      
        if (!$this->request->isAjax()){
			return $this->display('otherExpense');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$subject = $this->request->param('subject', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');

            if(!empty($subject)){
                $where[]= ['subject','=',$subject];
            }  
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('other_expense')->where($where)->count();
				$list =  Db::name('other_expense')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
					foreach ($list as $key => $value) {
						$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*佣金明细报表*/
	function commissionReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('commissionReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('commission_log')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
					if(!empty($list)){
						foreach ($list as $key => $value) {
							$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
						}
					}
				}else{
					$list =  Db::name('commission_log')->where($where)->order('created_time desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['介绍人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['introducer_id'],
            				"\t".$value['amount'],
            				"\t".date('Y-m-d H:i:s',$value['created_time']),
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*佣金月报表*/
	function commissionReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('commissionReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('commission_log')->where($where)->group('introducer_id,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_ym,SUM(amount) AS amount')->group('introducer_id,created_ym')->limit($limit)->order('created_ym desc')->select()->toArray();
				}else{
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_ym,SUM(amount) AS amount')->group('introducer_id,created_ym')->order('created_ym desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['介绍人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['introducer_id'],
            				"\t".$value['amount'],
            				"\t".$value['created_ym'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*佣金年报表*/
	function commissionReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('commissionReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$where[]=['created_y','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('commission_log')->where($where)->group('introducer_id,created_y')->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_y,SUM(amount) AS amount')->group('introducer_id,created_y')->limit($limit)->order('created_y desc')->select()->toArray();
				}else{
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_y,SUM(amount) AS amount')->group('introducer_id,created_y')->order('created_y desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['介绍人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['introducer_id'],
            				"\t".$value['amount'],
            				"\t".$value['created_y'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*业绩明细报表*/
	function performanceReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('performanceReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('performance_log')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
					if(!empty($list)){
						foreach ($list as $key => $value) {
							$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
						}
					}					
				}else{
					$list =  Db::name('performance_log')->where($where)->order('created_time desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['承办人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['director_id'],
            				"\t".$value['amount'],
            				"\t".date('Y-m-d H:i:s',$value['created_time']),
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业绩月报表*/
	function performanceReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('performanceReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('performance_log')->where($where)->group('director_id,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_ym,SUM(amount) AS amount')->group('director_id,created_ym')->limit($limit)->order('created_ym desc')->select()->toArray();					
				}else{
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_ym,SUM(amount) AS amount')->group('director_id,created_ym')->order('created_ym desc')->select()->toArray();	
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['承办人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['director_id'],
            				"\t".$value['amount'],
            				"\t".$value['created_ym'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);				
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*业绩年报表*/
	function performanceReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('performanceReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	$where[]=['created_y','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('performance_log')->where($where)->group('director_id,created_y')->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_y,SUM(amount) AS amount')->group('director_id,created_y')->limit($limit)->order('created_y desc')->select()->toArray();					
				}else{
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_y,SUM(amount) AS amount')->group('director_id,created_y')->order('created_y desc')->select()->toArray();
										$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['承办人','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['director_id'],
            				"\t".$value['amount'],
            				"\t".$value['created_y'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);	
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	


	/*收入明细报表*/
	function incomeReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('incomeReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
			$where[]=['type','=',2];

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('turnover')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
					if(!empty($list)){
						foreach ($list as $key => $value) {
							$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
						}
					}					
				}else{
					$list =  Db::name('turnover')->where($where)->order('created_time desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['科目','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['subject'],
            				"\t".$value['amount'],
            				"\t".date('Y-m-d H:i:s',$value['created_time']),
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*收入月报表*/
	function incomeReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('incomeReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
			$where[]=['type','=',2];

            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('turnover')->where($where)->group('subject,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->field('subject,created_ym,SUM(amount) AS amount')->group('subject,created_ym')->limit($limit)->order('created_ym desc')->select()->toArray();					
				}else{
					$list =  Db::name('turnover')->where($where)->field('subject,created_ym,SUM(amount) AS amount')->group('subject,created_ym')->order('created_ym desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['科目','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['subject'],
            				"\t".$value['amount'],
            				"\t".$value['created_ym'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);										
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*收入年报表*/
	function incomeReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('incomeReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$where[]=['type','=',2];
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('turnover')->where($where)->group('subject,created_y')->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->field('subject,created_y,SUM(amount) AS amount')->group('subject,created_y')->limit($limit)->order('created_y desc')->select()->toArray();
				}else{
					$list =  Db::name('turnover')->where($where)->field('subject,created_y,SUM(amount) AS amount')->group('subject,created_y')->order('created_y desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['科目','金额','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['subject'],
            				"\t".$value['amount'],
            				"\t".$value['created_y'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业务所得&代付费用明细*/
	function businessIncomeAndPayForReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('businessIncomeAndPayForReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('business_income')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->limit($limit)->order('created_time desc')->select()->toArray();
					if(!empty($list)){
						$customer_ids=array_column($list,'customer_id');
						$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile','id');
						foreach ($list as $key => $value) {
							$list[$key]['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
							$list[$key]['mobile']=$customer_list[$value['customer_id']]['mobile'];
							$list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['created_time']);
						}
					}					
				}else{
					$list =  Db::name('business_income')->where($where)->order('created_time desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['身份证','姓名','业务所得','代付费用','时间'];
            		fputcsv($fp,$header);
					foreach ($list as $key => $value) {
						$customer_id=$value['customer_id'];
						$customer= Db::name('customer')->where('id','=',$customer_id)->field('id,first_name,last_name,mobile')->find();
						if(!empty($customer)){
							$full_name=$customer['last_name'].$customer['first_name'];
							$mobile=$customer['mobile'];
						}else{
							$full_name='';
							$mobile='';
						}
            			$data=[
            				"\t".$value['customer_idcard'],
            				"\t".$full_name,
            				"\t".$value['business_income_amount'],
            				"\t".$value['pay_for_amount'],
            				"\t".date('Y-m-d H:i:s',$value['created_time']),
            			];
            			fputcsv($fp, $data);
					}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业务所得&代付费用月报表*/
	function  businessIncomeAndPayForReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('businessIncomeAndPayForReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('business_income')->where($where)->group('created_ym')->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->field('created_ym,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('created_ym')->limit($limit)->order('created_ym desc')->select()->toArray();	
				}else{
					$list =  Db::name('business_income')->where($where)->field('created_ym,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('created_ym')->order('created_ym desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['业务所得','代付费用','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['business_income_amount'],
            				"\t".$value['pay_for_amount'],
            				"\t".$value['created_ym'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);					
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*业务所得&代付费用年报表*/
	function businessIncomeAndPayForReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('businessIncomeAndPayForReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$where[]=['created_y','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('business_income')->where($where)->group('created_y')->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->field('created_y,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('created_y')->limit($limit)->order('created_y desc')->select()->toArray();					
				}else{
					$list =  Db::name('business_income')->where($where)->field('created_y,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('created_y')->order('created_y desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['业务所得','代付费用','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['business_income_amount'],
            				"\t".$value['pay_for_amount'],
            				"\t".$value['created_y'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*损益月报表*/
	function lossProfitReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('lossProfitReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$where[]=['created_ym','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('profit_loss_log')->where($where)->group('subject,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('profit_loss_log')->where($where)->field('subject,created_ym,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_ym')->limit($limit)->order('created_ym desc')->select()->toArray();					
				}else{
					$list =  Db::name('profit_loss_log')->where($where)->field('subject,created_ym,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_ym')->order('created_ym desc')->select()->toArray();
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['科目','收入','支出','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['subject'],
            				"\t".$value['income_amount'],
            				"\t".$value['outcome_amount'],
            				"\t".$value['created_ym'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*损益年报表*/
	function lossProfitReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('lossProfitReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$where[]=['created_y','>=',str_replace('-','', $start_time)];
            }
            if(!empty($end_time)){
              	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			try{
				$count=Db::name('profit_loss_log')->where($where)->group('subject,created_y')->count();
				if(empty($export)){
					$list =Db::name('profit_loss_log')->where($where)->field('subject,created_y,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_y')->limit($limit)->order('created_y desc')->select()->toArray();					
				}else{
					$list =Db::name('profit_loss_log')->where($where)->field('subject,created_y,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_y')->order('created_y desc')->select()->toArray();				
					$base_path=app()->getRootPath().'public/export/';
					$base_path=str_replace('\\','/',$base_path);
					$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
            		$file_name=$base_path.$original_name;
            		$fp = fopen($file_name, 'w');
            		fwrite($fp,"\xEF\xBB\xBF");
            		$header=['科目','收入','支出','时间'];
            		fputcsv($fp,$header);
            		foreach ($list as $key => $value) {
            			$data=[
            				"\t".$value['subject'],
            				"\t".$value['income_amount'],
            				"\t".$value['outcome_amount'],
            				"\t".$value['created_y'],
            			];
            			fputcsv($fp, $data);
            		}
            		fclose($fp);
            		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}
}